3.07. Шпаргалка с типичными задачами по SQL

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Пример базы данных

Таблица: categories

column_name	data_type	constraints	description
id	integer	PRIMARY KEY	Уникальный идентификатор категории
name	varchar(100)	NOT NULL UNIQUE	Название категории
parent_id	integer	FOREIGN KEY REFERENCES categories(id)	Ссылка на родительскую категорию
created_at	timestamp	DEFAULT CURRENT_TIMESTAMP	Дата создания

Таблица: products

column_name	data_type	constraints	description
id	integer	PRIMARY KEY	Уникальный идентификатор товара
name	varchar(200)	NOT NULL	Название товара
description	text		Описание товара
price	numeric(10,2)	NOT NULL CHECK (price >= 0)	Цена товара
category_id	integer	FOREIGN KEY REFERENCES categories(id) ON DELETE SET NULL	Связь с категорией
is_active	boolean	DEFAULT true	Статус доступности
created_at	timestamp	DEFAULT CURRENT_TIMESTAMP	Дата создания
updated_at	timestamp	DEFAULT CURRENT_TIMESTAMP	Дата обновления

Таблица: users

column_name	data_type	constraints	description
id	integer	PRIMARY KEY	Уникальный идентификатор пользователя
email	varchar(255)	NOT NULL UNIQUE	Email пользователя
phone	varchar(20)		Номер телефона
full_name	varchar(200)	NOT NULL	Полное имя
registration_date	timestamp	DEFAULT CURRENT_TIMESTAMP	Дата регистрации
is_verified	boolean	DEFAULT false	Статус подтверждения

Таблица: orders

column_name	data_type	constraints	description
id	integer	PRIMARY KEY	Уникальный идентификатор заказа
user_id	integer	NOT NULL FOREIGN KEY REFERENCES users(id) ON DELETE CASCADE	Связь с пользователем
order_number	varchar(50)	NOT NULL UNIQUE	Номер заказа
status	varchar(20)	DEFAULT 'new' CHECK (status IN ('new','processing','shipped','delivered','cancelled'))	Статус заказа
total_amount	numeric(12,2)		Итоговая сумма заказа
created_at	timestamp	DEFAULT CURRENT_TIMESTAMP	Дата создания заказа
updated_at	timestamp	DEFAULT CURRENT_TIMESTAMP	Дата обновления

Таблица: order_items

column_name	data_type	constraints	description
id	integer	PRIMARY KEY	Уникальный идентификатор позиции
order_id	integer	NOT NULL FOREIGN KEY REFERENCES orders(id) ON DELETE CASCADE	Связь с заказом
product_id	integer	NOT NULL FOREIGN KEY REFERENCES products(id)	Связь с товаром
quantity	integer	NOT NULL CHECK (quantity > 0)	Количество единиц товара
unit_price	numeric(10,2)	NOT NULL	Цена на момент покупки
subtotal	numeric(12,2)	GENERATED ALWAYS AS (quantity * unit_price) STORED	Подытог позиции

Связи между таблицами:

• categories → categories: иерархическая связь через parent_id (1 ко многим)
• categories → products: одна категория содержит много товаров (1 ко многим)
• users → orders: один пользователь оформляет много заказов (1 ко многим)
• orders → order_items: один заказ содержит много позиций (1 ко многим)
• products → order_items: один товар встречается во многих позициях заказов (1 ко многим)

---

Шаблон SELECT-запроса

SELECT столбец1, столбец2, агрегатная_функция(столбец)
FROM основная_таблица
JOIN связанная_таблица ON условие_соединения
WHERE фильтрация_строк
GROUP BY группировка_по_столбцам
HAVING фильтрация_сгруппированных_данных
ORDER BY сортировка_результата
LIMIT количество_записей OFFSET пропуск_записей;

---

Проектирование связей между таблицами

Связь один-к-одному (1:1)

CREATE TABLE users (
    id integer PRIMARY KEY,
    email varchar(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id integer PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    avatar_url varchar(500),
    bio text
);

Связь один-ко-многим (1:M)

CREATE TABLE categories (
    id integer PRIMARY KEY,
    name varchar(100) NOT NULL
);

CREATE TABLE products (
    id integer PRIMARY KEY,
    name varchar(200) NOT NULL,
    category_id integer REFERENCES categories(id)
);

Связь многие-ко-многим (M:N)

CREATE TABLE products (
    id integer PRIMARY KEY,
    name varchar(200) NOT NULL
);

CREATE TABLE tags (
    id integer PRIMARY KEY,
    name varchar(50) UNIQUE NOT NULL
);

CREATE TABLE product_tags (
    product_id integer REFERENCES products(id) ON DELETE CASCADE,
    tag_id integer REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);

Иерархическая связь (самоссылка)

CREATE TABLE categories (
    id integer PRIMARY KEY,
    name varchar(100) NOT NULL,
    parent_id integer REFERENCES categories(id) ON DELETE SET NULL
);

---

Базовые операции выборки данных

Выбор всех записей и всех столбцов

SELECT * FROM products;

Выбор конкретных столбцов

SELECT id, name, price FROM products;

Выбор с псевдонимами столбцов

SELECT 
    id AS product_id,
    name AS product_name,
    price AS current_price
FROM products;

Фильтрация по одному условию

SELECT name, price 
FROM products 
WHERE price > 1000;

Фильтрация по строковому значению

SELECT * FROM users WHERE email = 'user@example.com';

Фильтрация с LIKE для поиска по шаблону

SELECT name FROM products WHERE name LIKE '%смартфон%';
SELECT name FROM products WHERE name ILIKE 'Apple%'; -- регистронезависимый поиск

Фильтрация с IN для включения значений

SELECT * FROM orders WHERE status IN ('new', 'processing', 'shipped');

Фильтрация с NOT IN для исключения значений

SELECT * FROM products WHERE category_id NOT IN (5, 10, 15);

Фильтрация с AND для множественных условий

SELECT * FROM products 
WHERE price >= 500 
  AND price <= 5000 
  AND is_active = true;

Фильтрация с OR для альтернативных условий

SELECT * FROM orders 
WHERE status = 'cancelled' OR total_amount > 50000;

Комбинация AND и OR с группировкой условий

SELECT * FROM products 
WHERE category_id = 3 
  AND (price < 1000 OR is_active = false);

Фильтрация по диапазону значений с BETWEEN

SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

Фильтрация по NULL и NOT NULL

SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;

---

Агрегация и группировка данных

Подсчет общего количества записей

SELECT COUNT(*) AS total_products FROM products;

Подсчет записей по условию

SELECT COUNT(*) AS active_products 
FROM products 
WHERE is_active = true;

Подсчет уникальных значений

SELECT COUNT(DISTINCT category_id) AS categories_with_products 
FROM products;

Сумма, среднее, минимум и максимум

SELECT 
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS average_order,
    MIN(total_amount) AS min_order,
    MAX(total_amount) AS max_order
FROM orders
WHERE status = 'delivered';

Группировка по одному столбцу

SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS average_price
FROM products
GROUP BY category_id;

Группировка по нескольким столбцам

SELECT 
    DATE(created_at) AS order_date,
    status,
    COUNT(*) AS orders_count,
    SUM(total_amount) AS daily_revenue
FROM orders
GROUP BY DATE(created_at), status;

Фильтрация сгруппированных данных с HAVING

SELECT 
    category_id,
    COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 10;

Комбинация WHERE и HAVING

SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
WHERE status != 'cancelled'
GROUP BY user_id
HAVING SUM(total_amount) > 10000;

---

Сортировка и ограничение результатов

Сортировка по одному столбцу по возрастанию

SELECT name, price FROM products ORDER BY price ASC;

Сортировка по убыванию

SELECT name, created_at FROM products ORDER BY created_at DESC;

Сортировка по нескольким столбцам

SELECT * FROM products 
ORDER BY category_id ASC, price DESC, name ASC;

Сортировка с NULL значениями

SELECT name, price FROM products ORDER BY price DESC NULLS LAST;
SELECT name, price FROM products ORDER BY price ASC NULLS FIRST;

Ограничение количества записей с LIMIT

SELECT name, price FROM products ORDER BY price DESC LIMIT 10;

Пропуск записей с OFFSET для пагинации

-- Страница 1
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 0;
-- Страница 2
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 20;
-- Страница 3
SELECT id, name FROM products ORDER BY id LIMIT 20 OFFSET 40;

Получение последних записей по дате

SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 100;

---

Операции соединения таблиц JOIN

INNER JOIN — пересечение таблиц

SELECT 
    o.order_number,
    u.full_name,
    o.total_amount,
    o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'delivered';

LEFT JOIN — все записи из левой таблицы

SELECT 
    c.name AS category_name,
    p.name AS product_name,
    p.price
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
ORDER BY c.name, p.price DESC;

RIGHT JOIN — все записи из правой таблицы

SELECT 
    p.name AS product_name,
    oi.order_id
FROM products p
RIGHT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.order_id IS NOT NULL;

FULL OUTER JOIN — объединение всех записей

SELECT 
    c.name AS category_name,
    p.name AS product_name
FROM categories c
FULL OUTER JOIN products p ON c.id = p.category_id
WHERE c.parent_id IS NULL OR p.category_id IS NULL;

Множественные JOIN в одном запросе

SELECT 
    o.order_number,
    u.full_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    oi.subtotal
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status IN ('processing', 'shipped')
ORDER BY o.created_at DESC;

JOIN с агрегацией по связанным таблицам

SELECT 
    u.id,
    u.full_name,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.full_name
ORDER BY total_spent DESC;

---

Подзапросы в различных контекстах

Подзапрос в SELECT для вычисляемого столбца

SELECT 
    p.name,
    p.price,
    (SELECT AVG(price) FROM products WHERE category_id = p.category_id) AS category_avg_price
FROM products p;

Подзапрос в FROM как временная таблица

SELECT 
    category_stats.name,
    category_stats.product_count,
    category_stats.avg_price
FROM (
    SELECT 
        c.name,
        COUNT(p.id) AS product_count,
        AVG(p.price) AS avg_price
    FROM categories c
    LEFT JOIN products p ON c.id = p.category_id
    GROUP BY c.id, c.name
) AS category_stats
WHERE category_stats.product_count > 5;

Подзапрос в WHERE с оператором IN

SELECT name, price 
FROM products 
WHERE category_id IN (
    SELECT id FROM categories WHERE parent_id = 1
);

Подзапрос в WHERE с оператором EXISTS

SELECT u.full_name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.total_amount > 50000
);

Подзапрос в WHERE с оператором сравнения

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Подзапрос в HAVING для фильтрации групп

SELECT 
    category_id,
    COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > (
    SELECT AVG(product_count)
    FROM (
        SELECT COUNT(*) AS product_count
        FROM products
        GROUP BY category_id
    ) AS category_counts
);

Подзапрос в JOIN для виртуальной таблицы

SELECT 
    o.order_number,
    recent_orders.avg_amount,
    o.total_amount
FROM orders o
JOIN (
    SELECT 
        user_id,
        AVG(total_amount) AS avg_amount
    FROM orders
    WHERE status = 'delivered'
    GROUP BY user_id
) recent_orders ON o.user_id = recent_orders.user_id
WHERE o.total_amount > recent_orders.avg_amount;

Подзапрос в INSERT для вставки данных из другого запроса

INSERT INTO order_archive (order_id, user_id, total_amount, archived_at)
SELECT id, user_id, total_amount, CURRENT_TIMESTAMP
FROM orders
WHERE status = 'cancelled' AND created_at < CURRENT_DATE - INTERVAL '1 year';

Подзапрос в UPDATE для изменения на основе результатов запроса

UPDATE products p
SET price = price * 1.1
WHERE category_id IN (
    SELECT id FROM categories WHERE name LIKE '%премиум%'
);

Подзапрос в DELETE для удаления по условию из другого запроса

DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders 
    WHERE status = 'cancelled' AND created_at < CURRENT_DATE - INTERVAL '6 months'
);

---

Операции модификации данных DML

Вставка одной записи с возвратом идентификатора

INSERT INTO users (email, full_name, phone)
VALUES ('newuser@example.com', 'Иван Петров', '+79991234567')
RETURNING id, created_at;

Вставка нескольких записей одним запросом

INSERT INTO products (name, price, category_id, is_active)
VALUES 
    ('Товар 1', 1500.00, 3, true),
    ('Товар 2', 2300.50, 3, true),
    ('Товар 3', 890.00, 5, false);

Вставка с игнорированием дубликатов

INSERT INTO users (email, full_name)
VALUES ('user@example.com', 'Тест Пользователь')
ON CONFLICT (email) DO NOTHING;

Вставка с обновлением при конфликте UPSERT

INSERT INTO products (id, name, price, updated_at)
VALUES (123, 'Обновлённый товар', 2999.99, CURRENT_TIMESTAMP)
ON CONFLICT (id) DO UPDATE
SET 
    price = EXCLUDED.price,
    updated_at = CURRENT_TIMESTAMP
WHERE products.updated_at < EXCLUDED.updated_at;

Обновление одной записи по условию

UPDATE products
SET price = 1999.99, updated_at = CURRENT_TIMESTAMP
WHERE id = 456 AND is_active = true;

Обновление нескольких записей с возвратом изменённых данных

UPDATE orders
SET status = 'shipped', updated_at = CURRENT_TIMESTAMP
WHERE status = 'processing' AND created_at < CURRENT_DATE - INTERVAL '3 days'
RETURNING id, order_number, status;

Обновление с использованием данных из другой таблицы

UPDATE order_items oi
SET unit_price = p.price
FROM products p
WHERE oi.product_id = p.id 
  AND oi.order_id IN (SELECT id FROM orders WHERE status = 'new');

Удаление записей по условию с возвратом данных

DELETE FROM order_items
WHERE order_id = 789
RETURNING product_id, quantity;

Каскадное удаление через внешние ключи

-- При удалении пользователя удалятся все его заказы и позиции заказов
DELETE FROM users WHERE id = 100;

Массовое удаление с пакетной обработкой

-- Удаление большими порциями для избежания блокировок
WITH deleted AS (
    DELETE FROM orders
    WHERE id IN (
        SELECT id FROM orders 
        WHERE status = 'cancelled' 
        LIMIT 1000
    )
    RETURNING id
)
SELECT COUNT(*) FROM deleted;
-- Повторять запрос пока не будут удалены все нужные записи

---

Временные таблицы для сложных вычислений

Создание временной таблицы с данными

CREATE TEMP TABLE temp_order_summary (
    user_id integer,
    order_count integer,
    total_spent numeric(12,2),
    last_order_date timestamp
) ON COMMIT DROP;

Наполнение временной таблицы данными

INSERT INTO temp_order_summary
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent,
    MAX(created_at) AS last_order_date
FROM orders
WHERE status = 'delivered'
GROUP BY user_id;

Обновление данных во временной таблице

UPDATE temp_order_summary tos
SET total_spent = total_spent * 1.1
WHERE total_spent > 100000;

Использование временной таблицы в основном запросе

SELECT 
    u.full_name,
    u.email,
    tos.order_count,
    tos.total_spent,
    tos.last_order_date
FROM users u
JOIN temp_order_summary tos ON u.id = tos.user_id
ORDER BY tos.total_spent DESC;

Удаление временной таблицы после использования

DROP TABLE IF EXISTS temp_order_summary;

Автоматическое удаление временной таблицы при завершении транзакции

CREATE TEMP TABLE session_data (
    key varchar(100),
    value text,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
) ON COMMIT DROP;

---

Common Table Expressions (CTE) с WITH

Простой CTE для улучшения читаемости

WITH active_products AS (
    SELECT id, name, price, category_id
    FROM products
    WHERE is_active = true
)
SELECT 
    ap.name,
    ap.price,
    c.name AS category_name
FROM active_products ap
JOIN categories c ON ap.category_id = c.id
ORDER BY ap.price DESC;

Множественные CTE в одном запросе

WITH 
category_stats AS (
    SELECT 
        category_id,
        COUNT(*) AS product_count,
        AVG(price) AS avg_price
    FROM products
    GROUP BY category_id
),
top_categories AS (
    SELECT category_id
    FROM category_stats
    WHERE product_count >= 5 AND avg_price > 1000
)
SELECT 
    p.name,
    p.price,
    cs.product_count,
    cs.avg_price
FROM products p
JOIN category_stats cs ON p.category_id = cs.category_id
JOIN top_categories tc ON p.category_id = tc.category_id
ORDER BY p.price DESC;

Рекурсивный CTE для иерархических данных

WITH RECURSIVE category_tree AS (
    -- Базовый случай: корневые категории
    SELECT 
        id,
        name,
        parent_id,
        name AS path,
        1 AS level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Рекурсивный случай: дочерние категории
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        ct.path || ' > ' || c.name,
        ct.level + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
    id,
    name,
    path,
    level
FROM category_tree
ORDER BY path;

Рекурсивный CTE для поиска всех предков узла

WITH RECURSIVE ancestors AS (
    SELECT 
        id,
        name,
        parent_id,
        0 AS depth
    FROM categories
    WHERE id = 42
    
    UNION ALL
    
    SELECT 
        c.id,
        c.name,
        c.parent_id,
        a.depth + 1
    FROM categories c
    INNER JOIN ancestors a ON c.id = a.parent_id
)
SELECT name, depth FROM ancestors ORDER BY depth;

CTE с модификацией данных и возвратом результата

WITH moved_orders AS (
    UPDATE orders
    SET status = 'archived'
    WHERE created_at < CURRENT_DATE - INTERVAL '2 years'
      AND status IN ('delivered', 'cancelled')
    RETURNING id, user_id, total_amount
)
INSERT INTO orders_archive (order_id, user_id, amount, archived_at)
SELECT id, user_id, total_amount, CURRENT_TIMESTAMP
FROM moved_orders;

---

Оконные функции для аналитических запросов

Нумерация строк в рамках группы

SELECT 
    category_id,
    name,
    price,
    ROW_NUMBER() OVER (
        PARTITION BY category_id 
        ORDER BY price DESC
    ) AS rank_in_category
FROM products
WHERE is_active = true;

Ранжирование с пропуском номеров при равенстве

SELECT 
    user_id,
    total_amount,
    RANK() OVER (ORDER BY total_amount DESC) AS price_rank,
    DENSE_RANK() OVER (ORDER BY total_amount DESC) AS dense_rank
FROM orders
WHERE status = 'delivered';

Доступ к предыдущему и следующему значению

SELECT 
    DATE(created_at) AS order_date,
    SUM(total_amount) AS daily_revenue,
    LAG(SUM(total_amount)) OVER (ORDER BY DATE(created_at)) AS prev_day_revenue,
    LEAD(SUM(total_amount)) OVER (ORDER BY DATE(created_at)) AS next_day_revenue
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date;

Скользящее среднее значение

SELECT 
    DATE(created_at) AS order_date,
    AVG(total_amount) AS daily_avg,
    AVG(AVG(total_amount)) OVER (
        ORDER BY DATE(created_at)
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_moving_avg
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date;

Кумулятивная сумма

SELECT 
    DATE(created_at) AS order_date,
    SUM(total_amount) AS daily_revenue,
    SUM(SUM(total_amount)) OVER (
        ORDER BY DATE(created_at)
    ) AS cumulative_revenue
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date;

Процент от общей суммы в группе

SELECT 
    category_id,
    name,
    price,
    SUM(price) OVER (PARTITION BY category_id) AS category_total,
    ROUND(
        price * 100.0 / SUM(price) OVER (PARTITION BY category_id),
        2
    ) AS percent_of_category
FROM products
WHERE is_active = true;

Первый и последний элемент в окне

SELECT 
    user_id,
    created_at,
    total_amount,
    FIRST_VALUE(total_amount) OVER (
        PARTITION BY user_id 
        ORDER BY created_at
    ) AS first_order_amount,
    LAST_VALUE(total_amount) OVER (
        PARTITION BY user_id 
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM orders;

---

Хранимые функции и процедуры

Скалярная функция с возвратом значения

CREATE OR REPLACE FUNCTION calculate_age(birth_date date)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN DATE_PART('year', AGE(CURRENT_DATE, birth_date));
END;
$$;

-- Использование
SELECT full_name, calculate_age(registration_date) AS account_age_years
FROM users;

Функция с табличным возвратом

CREATE OR REPLACE FUNCTION get_user_orders(
    p_user_id integer,
    p_status varchar DEFAULT NULL
)
RETURNS TABLE (
    order_id integer,
    order_number varchar,
    total_amount numeric,
    created_at timestamp
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.id,
        o.order_number,
        o.total_amount,
        o.created_at
    FROM orders o
    WHERE o.user_id = p_user_id
      AND (p_status IS NULL OR o.status = p_status)
    ORDER BY o.created_at DESC;
END;
$$;

-- Использование
SELECT * FROM get_user_orders(123, 'delivered');

Процедура с параметрами и транзакцией

CREATE OR REPLACE PROCEDURE process_order(
    p_order_id integer,
    p_new_status varchar
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_old_status varchar;
    v_user_id integer;
BEGIN
    -- Сохраняем текущее состояние
    SELECT status, user_id INTO v_old_status, v_user_id
    FROM orders WHERE id = p_order_id;
    
    -- Обновляем статус
    UPDATE orders
    SET 
        status = p_new_status,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_order_id;
    
    -- Журналируем изменение
    INSERT INTO order_status_log (
        order_id,
        old_status,
        new_status,
        changed_at,
        changed_by
    ) VALUES (
        p_order_id,
        v_old_status,
        p_new_status,
        CURRENT_TIMESTAMP,
        current_user
    );
    
    -- Фиксируем транзакцию
    COMMIT;
END;
$$;

-- Вызов процедуры
CALL process_order(456, 'shipped');

Функция с обработкой исключений

CREATE OR REPLACE FUNCTION safe_divide(
    numerator numeric,
    denominator numeric,
    default_value numeric DEFAULT 0
)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    IF denominator = 0 THEN
        RETURN default_value;
    END IF;
    RETURN numerator / denominator;
EXCEPTION
    WHEN OTHERS THEN
        RETURN default_value;
END;
$$;

Функция для массовой обработки записей

CREATE OR REPLACE FUNCTION batch_update_prices(
    p_category_id integer,
    p_multiplier numeric,
    p_batch_size integer DEFAULT 1000
)
RETURNS TABLE (
    processed_count integer,
    iteration integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_updated integer;
    v_iteration integer := 0;
BEGIN
    LOOP
        v_iteration := v_iteration + 1;
        
        WITH updated AS (
            UPDATE products
            SET 
                price = price * p_multiplier,
                updated_at = CURRENT_TIMESTAMP
            WHERE id IN (
                SELECT id FROM products
                WHERE category_id = p_category_id
                  AND updated_at < CURRENT_TIMESTAMP - INTERVAL '1 hour'
                LIMIT p_batch_size
            )
            RETURNING id
        )
        SELECT COUNT(*) INTO v_updated FROM updated;
        
        processed_count := v_updated;
        iteration := v_iteration;
        RETURN NEXT;
        
        EXIT WHEN v_updated < p_batch_size;
        
        -- Пауза для снижения нагрузки
        PERFORM pg_sleep(0.1);
    END LOOP;
END;
$$;

-- Использование
SELECT * FROM batch_update_prices(5, 1.15, 500);

---

Управление схемой данных DDL

Добавление колонки без блокировки таблицы

-- Добавление колонки с значением по умолчанию NULL (быстро)
ALTER TABLE products ADD COLUMN discount_percent numeric(5,2);

-- Заполнение данных в фоновом режиме
UPDATE products SET discount_percent = 0 WHERE discount_percent IS NULL;

-- Установка NOT NULL после заполнения
ALTER TABLE products ALTER COLUMN discount_percent SET NOT NULL;

-- Установка значения по умолчанию
ALTER TABLE products ALTER COLUMN discount_percent SET DEFAULT 0;

Изменение типа колонки с минимальным простоем

-- Создание новой колонки нужного типа
ALTER TABLE orders ADD COLUMN total_amount_big numeric(15,2);

-- Копирование данных
UPDATE orders SET total_amount_big = total_amount;

-- Переименование колонок
ALTER TABLE orders RENAME COLUMN total_amount TO total_amount_old;
ALTER TABLE orders RENAME COLUMN total_amount_big TO total_amount;

-- Удаление старой колонки
ALTER TABLE orders DROP COLUMN total_amount_old;

Добавление ограничения с проверкой существующих данных

-- Проверка наличия ограничения перед добавлением
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'check_price_positive'
    ) THEN
        ALTER TABLE products 
        ADD CONSTRAINT check_price_positive 
        CHECK (price >= 0);
    END IF;
END $$;

Условное создание индекса

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_category_active 
ON products (category_id, is_active) 
WHERE is_active = true;

Изменение внешнего ключа без блокировки

-- Добавление нового FK без валидации существующих данных
ALTER TABLE order_items 
ADD CONSTRAINT fk_order_items_product_new 
FOREIGN KEY (product_id) REFERENCES products(id) 
NOT VALID;

-- Валидация в отдельной транзакции
ALTER TABLE order_items 
VALIDATE CONSTRAINT fk_order_items_product_new;

-- Удаление старого FK
ALTER TABLE order_items 
DROP CONSTRAINT IF EXISTS fk_order_items_product_old;

Проверка заполнения N полей из M

-- Ограничение на уровне таблицы
ALTER TABLE users ADD CONSTRAINT check_contact_info 
CHECK (
    CASE WHEN email IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN address IS NOT NULL THEN 1 ELSE 0 END >= 1
);

Управление enum-типами

-- Добавление нового значения в enum
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunded' AFTER 'delivered';

-- Удаление значения из enum (требует пересоздания типа)
-- Создаём новый тип без ненужного значения
CREATE TYPE order_status_new AS ENUM ('new', 'processing', 'shipped', 'delivered', 'cancelled');

-- Меняем тип колонки
ALTER TABLE orders 
ALTER COLUMN status TYPE order_status_new 
USING status::text::order_status_new;

-- Удаляем старый тип и переименовываем новый
DROP TYPE order_status;
ALTER TYPE order_status_new RENAME TO order_status;

Поиск упоминаний объекта в базе данных

-- Поиск в функциях и процедурах
SELECT 
    routine_schema,
    routine_name,
    routine_type
FROM information_schema.routines
WHERE routine_definition ILIKE '%products%';

-- Поиск в представлениях
SELECT 
    table_schema,
    table_name
FROM information_schema.views
WHERE view_definition ILIKE '%products%';

-- Поиск в триггерах
SELECT 
    trigger_schema,
    trigger_name,
    event_object_table
FROM information_schema.triggers
WHERE action_statement ILIKE '%products%';

-- Поиск в ограничениях
SELECT 
    tc.table_schema,
    tc.table_name,
    tc.constraint_name,
    ccu.table_name AS foreign_table
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu 
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_name ILIKE '%products%'
   OR ccu.table_name ILIKE '%products%';

Восстановление последовательности

-- Сброс последовательности к максимальному значению в таблице
SELECT setval(
    'products_id_seq',
    COALESCE((SELECT MAX(id) FROM products), 0) + 1,
    false
);

-- Проверка текущего значения последовательности
SELECT last_value, is_called FROM products_id_seq;

-- Просмотр всех последовательностей в схеме
SELECT 
    sequence_schema,
    sequence_name,
    last_value,
    increment_by
FROM information_schema.sequences
WHERE sequence_schema = 'public';

---

Работа с индексами

Создание индекса без блокировки таблицы

CREATE INDEX CONCURRENTLY idx_orders_user_created 
ON orders (user_id, created_at DESC);

Составной уникальный индекс с NULL-значениями

-- Уникальность только для не-NULL значений
CREATE UNIQUE INDEX idx_products_sku_active 
ON products (sku) 
WHERE is_active = true;

-- Или с частичным уникальным ограничением
CREATE UNIQUE INDEX idx_users_email_verified 
ON users (email) 
WHERE is_verified = true;

Частичный индекс для оптимизации запросов

-- Индекс только для активных товаров
CREATE INDEX idx_products_active_category 
ON products (category_id, price) 
WHERE is_active = true;

-- Индекс для недавних заказов
CREATE INDEX idx_orders_recent 
ON orders (created_at, status) 
WHERE created_at >= CURRENT_DATE - INTERVAL '90 days';

Индекс на выражении

-- Индекс для регистронезависимого поиска
CREATE INDEX idx_users_email_lower 
ON users (LOWER(email));

-- Использование
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Индекс на дате без времени
CREATE INDEX idx_orders_date_only 
ON orders ((DATE(created_at)));

Временное отключение индекса

-- Отключение индекса для массовой загрузки данных
ALTER INDEX idx_products_category DISABLE;

-- Массовая вставка
INSERT INTO products SELECT * FROM staging_products;

-- Включение индекса обратно
ALTER INDEX idx_products_category ENABLE;

-- Перестроение индекса после массовой операции
REINDEX INDEX CONCURRENTLY idx_products_category;

Анализ использования индексов

-- Статистика по индексам таблицы
SELECT 
    indexrelname AS index_name,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE schemaname = 'public' 
  AND relname = 'products'
ORDER BY idx_scan DESC;

-- Индексы, которые не используются
SELECT 
    schemaname,
    tablename,
    indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0 
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

---

Администрирование и мониторинг

Просмотр активных запросов

SELECT 
    pid,
    usename AS username,
    client_addr,
    application_name,
    state,
    query_start,
    NOW() - query_start AS duration,
    LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

Завершение зависших процессов

-- Завершение конкретного процесса
SELECT pg_terminate_backend(12345);

-- Завершение долгих запросов от определённого пользователя
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user'
  AND state = 'active'
  AND NOW() - query_start > INTERVAL '30 minutes'
  AND query NOT ILIKE '%pg_stat_activity%';

Блокировки и ожидания

-- Просмотр блокировок
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity 
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Статистика размера таблиц

SELECT 
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size,
    n_live_tup AS row_estimate
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Быстрый подсчёт записей в большой таблице

-- Приблизительный быстрый подсчёт
SELECT reltuples::bigint AS estimate 
FROM pg_class 
WHERE relname = 'products';

-- Точный подсчёт с анализом
SELECT COUNT(*) FROM products;

-- Подсчёт с условием и использованием индекса
SELECT COUNT(*) FROM products WHERE category_id = 5 AND is_active = true;

Ресурсоёмкие запросы

-- Запросы с наибольшим временем выполнения
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Запросы, создающие временные файлы
SELECT 
    query,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_read > 0 OR temp_blks_written > 0
ORDER BY temp_blks_written DESC;

Параметры конфигурации

-- Просмотр текущих настроек
SELECT name, setting, unit, context 
FROM pg_settings 
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'max_connections'
);

-- Изменение параметра на уровне сессии
SET work_mem = '64MB';

-- Изменение параметра на уровне базы данных
ALTER DATABASE mydb SET work_mem = '64MB';

-- Изменение параметра в конфигурации (требует перезагрузки)
-- ALTER SYSTEM SET shared_buffers = '4GB';
-- SELECT pg_reload_conf();

Автовакуум и его мониторинг

-- Активные процессы автовакуума
SELECT 
    pid,
    query,
    NOW() - query_start AS duration,
    state
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
  AND state = 'active';

-- Таблицы, нуждающиеся в вакууме
SELECT 
    schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    last_vacuum,
    last_autovacuum,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Управление WAL-файлами

-- Просмотр информации о WAL
SELECT 
    pg_current_wal_lsn() AS current_wal,
    pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;

-- Принудительное переключение WAL-файла
SELECT pg_switch_wal();

-- Просмотр архивных WAL-файлов
-- (требует доступа к файловой системе или настройки archive_command)

Репликация и отставание

-- На мастере: статус реплик
SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    NOW() - reply_time AS replication_lag
FROM pg_stat_replication;

-- На реплике: статус восстановления
SELECT 
    pg_is_in_recovery(),
    pg_last_wal_receive_lsn(),
    pg_last_wal_replay_lsn(),
    pg_last_xact_replay_timestamp();

Управление ролями и объектами

-- Поиск объектов, принадлежащих роли
SELECT 
    n.nspname AS schema_name,
    c.relname AS object_name,
    c.relkind AS object_type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relowner = (SELECT oid FROM pg_roles WHERE rolname = 'old_user');

-- Передача прав на объекты другой роли
REASSIGN OWNED BY old_user TO new_user;

-- Удаление оставшихся привилегий
DROP OWNED BY old_user;

-- Удаление роли
DROP ROLE old_user;

Сравнение конфигураций двух баз данных

-- Экспорт настроек для сравнения
SELECT name, setting 
FROM pg_settings 
WHERE source != 'default'
ORDER BY name;

-- Сравнение версий и расширений
SELECT 
    version(),
    current_setting('server_version_num')::int AS version_num;

SELECT 
    extname,
    extversion,
    extnamespace::regnamespace AS schema_name
FROM pg_extension
ORDER BY extname;

---

Валидация данных и целостность

Проверка формата email через регулярное выражение

SELECT email
FROM users
WHERE email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

Поиск записей с некорректным email

SELECT id, email
FROM users
WHERE email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
  AND email IS NOT NULL;

Очистка таблицы от записей с некорректными email

DELETE FROM users
WHERE email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
  AND email IS NOT NULL;

Валидация цвета в формате CSS (HEX)

SELECT color_code
FROM design_assets
WHERE color_code ~* '^#([A-F0-9]{6}|[A-F0-9]{3})$';

Проверка ИНН организации (10 цифр)

SELECT inn
FROM companies
WHERE inn ~ '^\d{10}$'
  AND LENGTH(inn) = 10;

Проверка ИНН физического лица (12 цифр)

SELECT inn
FROM contractors
WHERE inn ~ '^\d{12}$'
  AND LENGTH(inn) = 12;

Валидация номера телефона через маску

SELECT phone
FROM users
WHERE phone ~ '^\+7\(\d{3}\)\d{3}-\d{2}-\d{2}$';

Обновление поля только при явной передаче значения

UPDATE users
SET 
    phone = COALESCE(NULLIF(:new_phone, ''), phone),
    updated_at = CURRENT_TIMESTAMP
WHERE id = :user_id;

Использование доменов для типизации данных

CREATE DOMAIN email_address AS VARCHAR(255)
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE contacts (
    id integer PRIMARY KEY,
    user_email email_address NOT NULL
);

---

Работа с JSON и JSONB

Извлечение значения по ключу

SELECT 
    id,
    data->>'name' AS user_name,
    data->'address'->>'city' AS city
FROM users
WHERE data IS NOT NULL;

Поиск записей по наличию ключа в JSON

SELECT *
FROM events
WHERE payload ? 'error_code';

Поиск записей по значению внутри JSON массива

SELECT *
FROM orders
WHERE items @> '[{"product_id": 123}]';

Сравнение двух JSON объектов и поиск отличий

SELECT 
    jsonb_each_text(old_data) AS old_val,
    jsonb_each_text(new_data) AS new_val
FROM audit_log
WHERE old_data IS DISTINCT FROM new_data;

Обновление конкретного ключа в JSONB поле

UPDATE users
SET data = jsonb_set(data, '{last_login}', to_jsonb(CURRENT_TIMESTAMP))
WHERE id = 42;

Удаление ключа из JSONB объекта

UPDATE users
SET data = data - 'temporary_token'
WHERE id = 42;

Индексация JSONB полей для ускорения поиска

CREATE INDEX idx_users_data_gin ON users USING GIN (data);
CREATE INDEX idx_users_data_path ON users USING GIN (data jsonb_path_ops);

Агрегация данных в JSON массив

SELECT 
    order_id,
    jsonb_agg(jsonb_build_object('product', product_name, 'qty', quantity)) AS items
FROM order_items
GROUP BY order_id;

---

Операции с массивами

Объединение массивов в одну строку

SELECT array_agg(product_name) AS product_list
FROM order_items
WHERE order_id = 100;

Пересечение двух массивов

SELECT ARRAY[1, 2, 3] &< ARRAY[3, 4, 5];
-- Результат: true (если есть общие элементы)

Получение уникальных элементов массива

SELECT ARRAY(SELECT DISTINCT unnest(ARRAY[1, 2, 2, 3, 3]));

Сортировка элементов массива

SELECT ARRAY(SELECT unnest(ARRAY[3, 1, 2]) ORDER BY 1);

Разница между двумя массивами

SELECT ARRAY[1, 2, 3] - ARRAY[2];
-- Результат: {1,3}

Поиск элемента внутри массива

SELECT * FROM products
WHERE tags @> ARRAY['new'];

Развертывание массива в набор строк

SELECT unnest(ARRAY['apple', 'banana', 'cherry']) AS fruit;

Проверка наличия любого элемента из массива

SELECT * FROM products
WHERE tags && ARRAY['sale', 'clearance'];

---

Полнотекстовый поиск

Создание вектора поиска

SELECT to_tsvector('russian', 'быстрая коричневая лиса');

Создание запроса поиска

SELECT to_tsquery('russian', 'быстрая & лиса');

Выполнение поиска по тексту

SELECT title, body
FROM articles
WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'база & данных');

Поиск с учетом морфологии и ранжирование

SELECT 
    title,
    ts_rank(to_tsvector('russian', body), query) AS rank
FROM articles, to_tsquery('russian', 'оптимизация') query
WHERE to_tsvector('russian', body) @@ query
ORDER BY rank DESC;

Подсветка найденных слов в тексте

SELECT 
    ts_headline('russian', body, to_tsquery('russian', 'поиск'), 'StartSel=<b>, StopSel=</b>') AS snippet
FROM articles
WHERE to_tsvector('russian', body) @@ to_tsquery('russian', 'поиск');

Создание материалализованного представления для поиска

CREATE MATERIALIZED VIEW articles_search AS
SELECT 
    id,
    to_tsvector('russian', title || ' ' || body) AS search_vector
FROM articles;

CREATE INDEX idx_articles_search ON articles_search USING GIN (search_vector);

Обновление материалализованного представления

REFRESH MATERIALIZED VIEW CONCURRENTLY articles_search;

Поиск с исправлением опечаток (trigram)

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT word, similarity(word, 'постгрес') AS sim
FROM words
WHERE word % 'постгрес'
ORDER BY sim DESC
LIMIT 5;

---

Гео-запросы и работа с координатами

Установка расширения PostGIS

CREATE EXTENSION IF NOT EXISTS postgis;

Вычисление расстояния между точками

SELECT 
    ST_Distance(
        ST_MakePoint(37.6176, 55.7558)::geography,
        ST_MakePoint(30.3158, 59.9391)::geography
    ) AS distance_meters;

Поиск ближайших объектов

SELECT 
    name,
    ST_Distance(location::geography, ST_MakePoint(37.6176, 55.7558)::geography) AS dist
FROM shops
ORDER BY dist
LIMIT 10;

Поиск объектов внутри радиуса

SELECT *
FROM shops
WHERE ST_DWithin(
    location::geography,
    ST_MakePoint(37.6176, 55.7558)::geography,
    5000 -- радиус в метрах
);

Получение площади полигона

SELECT ST_Area(polygon::geography) AS area_sq_meters
FROM regions
WHERE id = 1;

Проверка пересечения геометрий

SELECT a.name, b.name
FROM regions a, regions b
WHERE ST_Intersects(a.polygon, b.polygon)
  AND a.id != b.id;

---

Оптимизация выполнения запросов

Анализ плана выполнения запроса

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123;

Ускорение запросов с большим списком в IN

-- Использование временной таблицы вместо большого IN
CREATE TEMP TABLE target_ids (id integer);
INSERT INTO target_ids VALUES (1), (2), (3); -- массовая вставка

SELECT * FROM orders o
JOIN target_ids t ON o.user_id = t.id;

Использование существующих индексов

-- Проверка использования индекса
EXPLAIN SELECT * FROM products WHERE category_id = 5;
-- Убедиться в наличии Index Scan вместо Seq Scan

Оптимизация COUNT(*) для больших таблиц

-- Использование системных каталогов для приблизительного подсчета
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';

Разбиение большого запроса на части

-- Обработка данными порциями
DO $$
DECLARE
    batch_size integer := 10000;
    processed integer := 1;
BEGIN
    WHILE processed > 0 LOOP
        WITH deleted AS (
            DELETE FROM large_log_table
            WHERE id IN (
                SELECT id FROM large_log_table LIMIT batch_size
            )
            RETURNING id
        )
        SELECT COUNT(*) INTO processed FROM deleted;
        
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

Измерение времени выполнения функции

CREATE OR REPLACE FUNCTION measure_execution()
RETURNS void AS $$
DECLARE
    start_time timestamp;
    end_time timestamp;
BEGIN
    start_time := clock_timestamp();
    
    -- Выполнение целевой логики
    PERFORM heavy_operation();
    
    end_time := clock_timestamp();
    RAISE NOTICE 'Execution time: %', end_time - start_time;
END;
$$ LANGUAGE plpgsql;

Поиск дубликатов записей

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Добавление времени выполнения в результат запроса

SELECT 
    *,
    clock_timestamp() - statement_timestamp() AS query_duration
FROM orders
LIMIT 100;

Оценка объема данных запроса

SELECT 
    pg_relation_size('orders') AS table_size,
    pg_total_relation_size('orders') AS total_size_with_indexes;

Рекурсивный запрос для обхода дерева

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, t.level + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY level, name;

---

Модификация данных (DML) продвинутые техники

UPSERT: Вставка или Обновление

INSERT INTO users (id, email, name)
VALUES (1, 'test@example.com', 'Test')
ON CONFLICT (id) DO UPDATE
SET 
    email = EXCLUDED.email,
    name = EXCLUDED.name,
    updated_at = CURRENT_TIMESTAMP;

Вставка без увеличения последовательности при конфликте

INSERT INTO users (id, email)
VALUES (1, 'test@example.com')
ON CONFLICT (id) DO NOTHING;
-- Последовательность не расходуется при игнорировании

Массовая вставка через VALUES

INSERT INTO products (name, price)
VALUES 
    ('Item 1', 100),
    ('Item 2', 200),
    ('Item 3', 300);
-- Использование COPY для еще большей скорости при загрузке из файла

Возврат ID затронутых записей в связанных таблицах

WITH inserted_user AS (
    INSERT INTO users (email) VALUES ('new@test.com') RETURNING id
),
inserted_profile AS (
    INSERT INTO profiles (user_id, bio)
    SELECT id, 'Default bio' FROM inserted_user
    RETURNING user_id
)
SELECT * FROM inserted_profile;

Обновление данных из другой таблицы

UPDATE products p
SET price = src.new_price
FROM price_source src
WHERE p.id = src.product_id
  AND p.price IS DISTINCT FROM src.new_price;

Сохранение сгенерированного ID в другом поле

INSERT INTO documents (id, reference_code)
VALUES (DEFAULT, 'DOC-' || NEXTVAL('documents_id_seq'))
RETURNING id, reference_code;

Последовательные запросы с использованием ID

-- В рамках одной транзакции или функции
INSERT INTO orders (user_id) VALUES (1) RETURNING id INTO v_order_id;
INSERT INTO order_items (order_id, product_id) VALUES (v_order_id, 5);

Обновление только при изменении данных

UPDATE users
SET name = 'New Name'
WHERE id = 1
  AND name IS DISTINCT FROM 'New Name';

Защита от перезаписи чужих изменений (Optimistic Locking)

UPDATE documents
SET content = 'New content', version = version + 1
WHERE id = 1
  AND version = 5; -- Проверка версии

Обновление нескольких записей разными данными

UPDATE products p
SET price = v.price
FROM (VALUES 
    (1, 100.00),
    (2, 200.00),
    (3, 300.00)
) AS v(id, price)
WHERE p.id = v.id;

Удаление миллионов записей порциями

-- Запускать циклически до завершения
DELETE FROM logs
WHERE ctid IN (
    SELECT ctid FROM logs
    WHERE created_at < '2023-01-01'
    LIMIT 5000
);

Ограничение количества зависимых записей

-- Использование триггера для проверки лимита
CREATE OR REPLACE FUNCTION check_item_limit()
RETURNS TRIGGER AS $$
DECLARE
    v_count integer;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM order_items
    WHERE order_id = NEW.order_id;
    
    IF v_count >= 100 THEN
        RAISE EXCEPTION 'Limit exceeded for order items';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_item_limit
BEFORE INSERT ON order_items
FOR EACH ROW EXECUTE FUNCTION check_item_limit();

Журналирование изменений таблицы

CREATE TABLE audit_log (
    table_name text,
    record_id integer,
    action text,
    old_data jsonb,
    new_data jsonb,
    changed_at timestamp DEFAULT CURRENT_TIMESTAMP,
    changed_by text DEFAULT current_user
);

CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data)
        VALUES (TG_TABLE_NAME, OLD.id, TG_OP, to_jsonb(OLD));
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, action, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, to_jsonb(NEW));
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Автоматическое обновление поля updated_at

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

---

Модификация схемы (DDL) безопасные методы

Добавление колонки с значением по умолчанию

-- Быстрое добавление (без пересчета таблицы)
ALTER TABLE products ADD COLUMN is_new boolean DEFAULT false;

Проверка существования ограничения перед созданием

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'unique_email'
    ) THEN
        ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
    END IF;
END $$;

Изменение типа колонки с минимальным влиянием

-- Шаг 1: Добавить новую колонку
ALTER TABLE orders ADD COLUMN total_big numeric(15,2);

-- Шаг 2: Заполнить данными
UPDATE orders SET total_big = total_amount;

-- Шаг 3: Переключить приложение на новую колонку (логически)

-- Шаг 4: Удалить старую колонку
ALTER TABLE orders DROP COLUMN total_amount;

-- Шаг 5: Переименовать новую
ALTER TABLE orders RENAME COLUMN total_big TO total_amount;

Удаление значения из ENUM типа

-- Создание нового типа
CREATE TYPE order_status_new AS ENUM ('new', 'processing', 'shipped');

-- Конвертация данных
ALTER TABLE orders 
ALTER COLUMN status TYPE order_status_new 
USING status::text::order_status_new;

-- Замена типа
DROP TYPE order_status;
ALTER TYPE order_status_new RENAME TO order_status;

Поиск зависимостей объекта

SELECT 
    dependent_ns.nspname AS dependent_schema,
    dependent.relname AS dependent_table,
    source_ns.nspname AS source_schema,
    source.relname AS source_table
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent ON pg_rewrite.ev_class = dependent.oid
JOIN pg_class AS source ON pg_depend.refobjid = source.oid
JOIN pg_namespace AS dependent_ns ON dependent.relnamespace = dependent_ns.oid
JOIN pg_namespace AS source_ns ON source.relnamespace = source_ns.oid
WHERE source.relname = 'users';

---

Администрирование и обслуживание

Просмотр активных процессов

SELECT 
    pid,
    usename,
    datname,
    client_addr,
    application_name,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle';

Завершение процесса

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;

Список всех функций и процедур

SELECT 
    routine_schema,
    routine_name,
    routine_type
FROM information_schema.routines
WHERE routine_schema = 'public';

Список внешних ключей

SELECT 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Статистика использования индексов

SELECT 
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    idx_tup_read AS tuples_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Список расширений

SELECT 
    extname,
    extversion,
    nspname AS schema_name
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace;

Размер таблиц и индексов

SELECT 
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS data_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Тяжелые запросы

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Запросы с временными файлами

SELECT 
    query,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC;

Параметры конфигурации

SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('work_mem', 'shared_buffers', 'effective_cache_size');

Процессы автовакуума

SELECT 
    pid,
    query,
    NOW() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

Отставание реплик

SELECT 
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Использование последовательностей

SELECT 
    sequencename,
    last_value,
    max_value,
    ROUND(100.0 * last_value / NULLIF(max_value, 0), 2) AS usage_percent
FROM pg_sequences;

Очистка WAL файлов

-- Настройка archive_cleanup_command в postgresql.conf
-- Или ручной вызов на реплике
SELECT pg_walfile_name(pg_last_wal_receive_lsn());

Удаление роли с объектами

REASSIGN OWNED BY old_role TO new_role;
DROP OWNED BY old_role;
DROP ROLE old_role;

Сравнение конфигураций

SELECT name, setting, source
FROM pg_settings
WHERE source != 'default'
ORDER BY name;

Копирование базы данных

-- Через утилиту командной строки
-- pg_dump -U user source_db | psql -U user target_db

Проверка синтаксиса SQL

-- Использование EXPLAIN без выполнения
EXPLAIN SELECT * FROM users WHERE id = 1;

Откат части транзакции

BEGIN;
SAVEPOINT my_savepoint;
-- Операции
ROLLBACK TO my_savepoint;
-- Продолжение работы
COMMIT;

Завершение простаивающих подключений

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND NOW() - state_change > INTERVAL '1 hour';

Блокировки и очереди

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.GRANTED;

Журналирование DDL команд

-- Настройка log_statement = 'ddl' в postgresql.conf
-- Или использование событийных триггеров
CREATE EVENT TRIGGER log_ddl_commands
ON ddl_command_end
EXECUTE FUNCTION log_ddl_function();

Статистика действий в таблице

SELECT 
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
ORDER BY relname;

---

Транзакции и управление блокировками

Начало и завершение транзакции

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Откат транзакции при ошибке

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Ошибка выполнения
ROLLBACK;

Использование точек сохранения (Savepoints)

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT sp_order_items;

INSERT INTO order_items (order_id, product_id) VALUES (1, 10);
-- Ошибка вставки позиции
ROLLBACK TO sp_order_items;

INSERT INTO order_items (order_id, product_id) VALUES (1, 11);

COMMIT;

Установка уровня изоляции транзакции

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Блокировка строк для обновления

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

Блокировка строк для чтения

SELECT * FROM accounts WHERE id = 1 FOR SHARE;

Определение текущих блокировок

SELECT 
    locktype,
    database,
    relation::regclass,
    page,
    tuple,
    virtualxid,
    transactionid,
    classid::regclass,
    objid,
    objsubid,
    virtualtransaction,
    pid,
    mode,
    granted
FROM pg_locks
WHERE granted = false;

Завершение транзакции с указанием цепи

COMMIT AND CHAIN;
ROLLBACK AND CHAIN;

---

Секционирование таблиц

Создание секционированной таблицы по диапазону

CREATE TABLE measurements (
    logdate date NOT NULL,
    peaktemp int,
    unitsales int
) PARTITION BY RANGE (logdate);

Создание секций для таблицы

CREATE TABLE measurements_y2023 PARTITION OF measurements
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE measurements_y2024 PARTITION OF measurements
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Создание секционированной таблицы по списку

CREATE TABLE cities (
    city_id int NOT NULL,
    name text,
    region text
) PARTITION BY LIST (region);

CREATE TABLE cities_europe PARTITION OF cities
    FOR VALUES IN ('EUROPE', 'EUR');

CREATE TABLE cities_asia PARTITION OF cities
    FOR VALUES IN ('ASIA', 'ASA');

Создание секционированной таблицы по хешу

CREATE TABLE users_hash (
    user_id int NOT NULL,
    username text
) PARTITION BY HASH (user_id);

CREATE TABLE users_hash_0 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_hash_1 PARTITION OF users_hash
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Добавление новой секции без блокировки основной таблицы

CREATE TABLE measurements_y2025 PARTITION OF measurements
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Отсоединение секции для архивации

ALTER TABLE measurements DETACH PARTITION measurements_y2023;

Присоединение существующей таблицы как секции

ALTER TABLE measurements ATTACH PARTITION measurements_y2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Запрос к секционированной таблице с указанием секции

SELECT * FROM measurements_y2024 WHERE logdate = '2024-05-01';

Автоматическое создание секций через функцию

CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
    partition_name text;
    start_date date;
    end_date date;
BEGIN
    start_date := DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month');
    end_date := start_date + INTERVAL '1 month';
    partition_name := 'measurements_' || TO_CHAR(start_date, 'YYYY_MM');

    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF measurements FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

---

Безопасность на уровне строк (RLS)

Включение RLS для таблицы

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

Создание политики для владельца записи

CREATE POLICY user_is_owner ON accounts
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::int);

Создание политики только для чтения

CREATE POLICY users_can_view ON accounts
    FOR SELECT
    USING (true);

Создание политики для модераторов

CREATE POLICY moderators_can_edit ON accounts
    FOR UPDATE
    USING (current_setting('app.user_role') = 'moderator');

Принудительное применение RLS для владельца таблицы

ALTER TABLE accounts FORCE ROW LEVEL SECURITY;

Просмотр активных политик

SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies
WHERE tablename = 'accounts';

Отключение RLS для таблицы

ALTER TABLE accounts DISABLE ROW LEVEL SECURITY;

---

Работа с датами и временными интервалами

Получение текущей даты и времени

SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT CURRENT_TIMESTAMP;
SELECT NOW();
SELECT clock_timestamp();

Извлечение частей даты

SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);
SELECT EXTRACT(DOW FROM CURRENT_TIMESTAMP); -- День недели
SELECT EXTRACT(ISOYEAR FROM CURRENT_TIMESTAMP);

Сложение и вычитание интервалов

SELECT CURRENT_DATE + INTERVAL '1 day';
SELECT CURRENT_DATE - INTERVAL '1 month';
SELECT CURRENT_TIMESTAMP + INTERVAL '2 hours 30 minutes';
SELECT CURRENT_DATE + INTERVAL '1 year 2 months 3 days';

Вычисление разницы между датами

SELECT AGE(CURRENT_TIMESTAMP, created_at) AS age FROM orders;
SELECT CURRENT_DATE - DATE '2023-01-01' AS days_diff;

Обрезка времени до начала периода

SELECT DATE_TRUNC('hour', CURRENT_TIMESTAMP);
SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP);
SELECT DATE_TRUNC('week', CURRENT_TIMESTAMP);
SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP);

Генерация серии дат

SELECT generate_series(
    '2024-01-01'::date,
    '2024-01-31'::date,
    '1 day'::interval
) AS date_value;

Конвертация временных зон

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Europe/Moscow';
SELECT TIMESTAMP '2024-01-01 12:00:00' AT TIME ZONE 'America/New_York';

Проверка високосного года

SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS year,
       CASE 
           WHEN EXTRACT(DAY FROM (DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year - 1 day')) = 366 
           THEN 'Leap' 
           ELSE 'Common' 
       END AS year_type;

Получение первого и последнего дня месяца

SELECT DATE_TRUNC('month', CURRENT_DATE) AS first_day;
SELECT (DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month - 1 day')::date AS last_day;

Работа с временными диапазонами (tsrange)

SELECT tsrange('2024-01-01', '2024-02-01') @> '2024-01-15';
SELECT tsrange('2024-01-01', '2024-02-01') && tsrange('2024-01-25', '2024-03-01');

---

Паттерны обслуживания и миграции

Расширение таблицы без блокировки (Add Column)

ALTER TABLE large_table ADD COLUMN new_column varchar(100);
-- Заполнение в фоне
UPDATE large_table SET new_column = 'default' WHERE new_column IS NULL;
ALTER TABLE large_table ALTER COLUMN new_column SET NOT NULL;

Переименование таблицы с минимальным простоем

ALTER TABLE old_name RENAME TO new_name;
-- Обновление зависимостей требуется вручную или через скрипт

Изменение типа данных колонки через новую колонку

ALTER TABLE users ADD COLUMN phone_new varchar(20);
UPDATE users SET phone_new = phone;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN phone_new TO phone;

Пересборка таблицы для освобождения места

VACUUM FULL VERBOSE users;
-- Или использование CLUSTER для физического упорядочивания
CLUSTER users USING idx_users_created_at;

Обновление статистики планировщика

ANALYZE VERBOSE orders;
ANALYZE VERBOSE products;

Проверка целостности данных

SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM users);
SELECT * FROM order_items WHERE order_id NOT IN (SELECT id FROM orders);

Архивация старых данных

INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

DELETE FROM orders
WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

Сжатие данных через TOAST

-- Автоматическое сжатие больших текстовых полей
-- Настройка уровня сжатия
ALTER TABLE documents ALTER COLUMN content SET STORAGE EXTENDED;

---

Мониторинг производительности

Поиск запросов с полным сканированием таблицы

SELECT 
    query,
    calls,
    total_exec_time
FROM pg_stat_statements
WHERE query LIKE '%Seq Scan%'
ORDER BY total_exec_time DESC
LIMIT 10;

Анализ кэш-хитов

SELECT 
    relname,
    heap_blks_read,
    heap_blks_hit,
    ROUND(100.0 * heap_blks_hit / NULLIF(heap_blks_read + heap_blks_hit, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY hit_ratio ASC;

Отслеживание роста таблиц

SELECT 
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||relname) DESC;

Проверка состояния индексов

SELECT 
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Выявление долгих транзакций

SELECT 
    pid,
    usename,
    NOW() - xact_start AS transaction_duration,
    state,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND NOW() - xact_start > INTERVAL '5 minutes';

Мониторинг подключений

SELECT 
    datname,
    count(*) as connections
FROM pg_stat_activity
GROUP BY datname;

Проверка лимитов подключений

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

---

Тестирование SQL запросов

Создание тестовых данных

INSERT INTO users (email, full_name)
SELECT 
    'user' || i || '@test.com',
    'User ' || i
FROM generate_series(1, 1000) AS i;

Очистка тестовых данных

TRUNCATE TABLE users RESTART IDENTITY CASCADE;

Проверка ожидаемого результата

SELECT COUNT(*) = 1000 AS test_passed FROM users;

Изоляция тестов в транзакции

BEGIN;
-- Тестовые запросы
ROLLBACK;

Сравнение результатов двух запросов

(SELECT * FROM query_a EXCEPT SELECT * FROM query_b)
UNION ALL
(SELECT * FROM query_b EXCEPT SELECT * FROM query_a);

---

Утилиты и вспомогательные функции

Генерация UUID

SELECT gen_random_uuid();
-- Требует расширения pgcrypto или uuid-ossp

Хеширование данных

SELECT md5('secret_string');
SELECT sha256('secret_string');

Сжатие и распаковка данных

SELECT compress('data');
SELECT decompress(compress('data'));

Работа с сетевыми адресами

SELECT '192.168.1.1'::inet;
SELECT '192.168.1.0/24'::cidr;
SELECT host('192.168.1.1'::inet);
SELECT masklen('192.168.1.0/24'::cidr);

Работа с векторами и массивами бит

SELECT '101'::bit(3);
SELECT '101'::varbit(5);

Обработка XML данных

SELECT xpath('/root/element/text()', '<root><element>value</element></root>');

Обработка YAML данных (при наличии расширения)

SELECT yaml_parse('key: value');

---

Расширенные возможности оконных функций

Вычисление процента от суммы

SELECT 
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2) AS percent_of_dept
FROM employees;

Скользящее среднее за 3 периода

SELECT 
    date,
    value,
    AVG(value) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM metrics;

Накопительный итог

SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

Доступ к первой и последней строке окна

SELECT 
    department,
    employee,
    salary,
    FIRST_VALUE(employee) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
    LAST_VALUE(employee) OVER (
        PARTITION BY department 
        ORDER BY salary DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_earner
FROM employees;

Нумерация строк для пагинации

SELECT * FROM (
    SELECT 
        id,
        name,
        ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num
    FROM products
) sub
WHERE row_num BETWEEN 21 AND 40;

Распределение по группам (NTILE)

SELECT 
    employee,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

Процентиль непрерывный

SELECT 
    employee,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank
FROM employees;

Процентиль дискретный

SELECT 
    employee,
    salary,
    CUME_DIST() OVER (ORDER BY salary) AS cumulative_dist
FROM employees;

---

Оптимизация соединений

Использование EXISTS вместо IN для больших наборов

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Использование LATERAL для зависимых соединений

SELECT 
    u.id,
    u.name,
    latest.order_date,
    latest.total
FROM users u
CROSS JOIN LATERAL (
    SELECT order_date, total
    FROM orders
    WHERE user_id = u.id
    ORDER BY order_date DESC
    LIMIT 1
) latest;

Соединение с условием диапазона

SELECT 
    e.name,
    d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.hired_at BETWEEN d.start_date AND d.end_date;

Соединение по неэквивалентным условиям

SELECT 
    a.id,
    b.id
FROM table_a a
JOIN table_b b ON a.value > b.value;

---

Работа с полнотекстовым поиском

Создание конфигурации поиска

CREATE TEXT SEARCH CONFIGURATION ru (COPY = russian);
ALTER TEXT SEARCH CONFIGURATION ru
    ALTER MAPPING FOR hword, hword_part, word
    WITH russian_stem;

Индексация текстовых полей

CREATE INDEX idx_products_search ON products USING GIN (to_tsvector('ru', name || ' ' || description));

Поиск с ранжированием

SELECT 
    id,
    name,
    ts_rank(to_tsvector('ru', name || ' ' || description), query) AS rank
FROM products, to_tsquery('ru', 'телефон') query
WHERE to_tsvector('ru', name || ' ' || description) @@ query
ORDER BY rank DESC;

Подсветка результатов

SELECT 
    ts_headline('ru', description, to_tsquery('ru', 'телефон'), 'StartSel=<b>, StopSel=</b>') AS snippet
FROM products
WHERE to_tsvector('ru', description) @@ to_tsquery('ru', 'телефон');

Словарь синонимов

CREATE TEXT SEARCH DICTIONARY syn_dict (TEMPLATE = synonym, SYNONYMS = my_synonyms);

Стоп-слова

CREATE TEXT SEARCH DICTIONARY stop_dict (TEMPLATE = stop, STOPWORDS = russian);

---

Управление расширениями

Установка расширения

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;
CREATE EXTENSION IF NOT EXISTS hstore;

Просмотр установленных расширений

SELECT * FROM pg_extension;

Обновление расширения

ALTER EXTENSION pg_trgm UPDATE TO '0.5';

Удаление расширения

DROP EXTENSION IF EXISTS pg_trgm;

Поиск объектов расширения

SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pg_trgm');

---

Резервное копирование и восстановление

Логическое резервное копирование

pg_dump -U username database_name > backup.sql
pg_dump -U username -t table_name database_name > table_backup.sql

Восстановление из дампа

psql -U username database_name < backup.sql

Физическое резервное копирование

pg_basebackup -U username -D /var/lib/postgresql/backup -Fp -Xs -P

Точечное восстановление (PITR)

# Настройка recovery_target_time в postgresql.conf
# recovery_target_time = '2024-01-01 12:00:00'

Репликация WAL-файлов

# Настройка archive_command
# archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

---

Конфигурация сервера

Просмотр настроек

SHOW ALL;
SELECT * FROM pg_settings;

Изменение настроек на лету

SET statement_timeout TO '1min';
SET lock_timeout TO '10s';

Изменение настроек в конфигурации

ALTER SYSTEM SET work_mem = '256MB';
SELECT pg_reload_conf();

Проверка версии сервера

SELECT version();
SELECT current_setting('server_version_num');

Просмотр путей к файлам

SHOW data_directory;
SHOW hba_file;
SHOW ident_file;

---

Диагностика проблем

Поиск блокирующих запросов

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Поиск дубликатов индексов

SELECT 
    pg_relation_size(i.indexrelid) AS index_size,
    i.indexrelname AS index_name,
    i.indrelname AS table_name
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE NOT indisunique
  AND EXISTS (
      SELECT 1 FROM pg_stat_user_indexes si 
      WHERE si.indrelid = i.indrelid 
        AND si.indexrelid != i.indexrelid
        AND pg_get_indexdef(si.indexrelid) = pg_get_indexdef(i.indexrelid)
  );

Поиск неиспользуемых индексов

SELECT 
    indexrelname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey';

Анализ вакуума

SELECT 
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE last_vacuum IS NULL OR last_autovacuum IS NULL;

Проверка целостности страниц

SELECT * FROM pg_stat_database WHERE datname = current_database();

Мониторинг репликации

SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

Проверка отставания последовательностей

SELECT 
    sequencename,
    last_value,
    max_value,
    CASE 
        WHEN max_value IS NULL THEN 'unlimited'
        ELSE ROUND(100.0 * last_value / max_value, 2) || '%'
    END AS usage
FROM pg_sequences;

---